﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// 用户组DAL
    /// </summary>
    public class PrvGroup
    {
        /// <summary>
        /// 获得数据列表
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="strWhere">sql查询语句</param>
        /// <returns>用户组列表</returns>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FGroupNo,FEntityNo,FGroupName,FNote ");
            strSql.Append(" FROM t_PrvGroup ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 根据公司编号获取数据列表
        /// <creator>wangl</creator>
        /// <date>2013-07-29</date>
        /// </summary>
        /// <param name="entityNo">公司编号</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetListByEntityNo(string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FGroupNo,FEntityNo,FGroupName,FNote ");
            strSql.Append(" FROM t_PrvGroup ");
            strSql.Append(" WHERE FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)};
            parameters[0].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 是否存在该记录
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fGroupNo">用户组编号</param>
        /// <param name="fEntityNo">组织机构代码</param>
        /// <returns>返回是否存额在</returns>
        public bool Exists(string fGroupNo, string fEntityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_PrvGroup");
            strSql.Append(" where FGroupNo=@FGroupNo and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = fGroupNo;
            parameters[1].Value = fEntityNo;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 根据用户组编号获取人员列表
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="groupID">用户组编号</param>
        /// <param name="fentityNo">组织机构代码</param>
        /// <returns>人员列表</returns>
        public List<string> GetLoginIDListByGroupID(string groupID, string fentityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FLogID ");
            strSql.Append(" FROM t_PrvUserGroup ");
            strSql.Append(" WHERE FGroupNo=@FGroupNo and FentityNo=@FentityNo");
            SqlParameter[] parameters = {
					new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10),
                    new SqlParameter("@FentityNo", SqlDbType.NVarChar,12)
                                           };
            parameters[0].Value = groupID;
            parameters[1].Value = fentityNo;
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            List<string> loginIDList = new List<string>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                loginIDList.Add(ds.Tables[0].Rows[i]["FLogID"].ToString());
            }

            return loginIDList;
        }
        /// <summary>
        /// 根据菜单编号获取人员列表
        /// 根据用户组编号获取
        /// <creator>wangl</creator>
        /// <date>2013-11-27</date>
        /// </summary>
        /// <param name="functionNo"></param>
        /// <param name="entityNo"></param>
        /// <returns></returns>
        public List<string> GetLoginIDListByFunctionNo(string functionNo, string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT DISTINCT a.FLogID FROM dbo.t_PrvUserGroup a,t_PrvGroupPriv b ");
            strSql.Append(" WHERE a.FGroupNo=b.FGroupNo AND  a.FEntityNo=b.FEntityNo AND a.FEntityNo=@FEntityNo AND b.FFunctNo=@FFunctNo ");
           
            SqlParameter[] parameters = {
					new SqlParameter("@FFunctNo", SqlDbType.NVarChar,80),
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)
                                           };
            parameters[0].Value = functionNo;
            parameters[1].Value = entityNo;
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            List<string> loginIDList = new List<string>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                loginIDList.Add(ds.Tables[0].Rows[i]["FLogID"].ToString());
            }

            return loginIDList;
        }
        /// <summary>
        /// 根据用户组编号获取
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="groupID">用户组编号</param>
        /// <param name="fentityNo">组织机构编号</param>
        /// <returns>用户组编号集合</returns>
        public List<string> GetFunctionNOListByGroupID(string groupID, string fentityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FFunctNo ");
            strSql.Append(" FROM t_PrvGroupPriv ");
            strSql.Append(" WHERE FGroupNo=@FGroupNo and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10),
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)
                                           };
            parameters[0].Value = groupID;
            parameters[1].Value = fentityNo;
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            List<string> functionNOList = new List<string>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                functionNOList.Add(ds.Tables[0].Rows[i]["FFunctNo"].ToString());
            }

            return functionNOList;
        }

        /// <summary>
        /// 得到一个对象实体
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="fGroupNo">用户组编号</param>
        /// <param name="fEntityNo">组织机构代码</param>
        /// <returns>用户组信息</returns>
        public Model.PrvGroup GetModel(string fGroupNo, string fEntityNo)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 FGroupNo,FEntityNo,FGroupName,FNote from t_PrvGroup ");
            strSql.Append(" where FGroupNo=@FGroupNo and FEntityNo=@FEntityNo ");
            SqlParameter[] parameters = {
					new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
            parameters[0].Value = fGroupNo;
            parameters[1].Value = fEntityNo;

            Model.PrvGroup model = new Model.PrvGroup();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["FGroupNo"] != null && ds.Tables[0].Rows[0]["FGroupNo"].ToString() != "")
                {
                    model.FGroupNo = ds.Tables[0].Rows[0]["FGroupNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FEntityNo"] != null && ds.Tables[0].Rows[0]["FEntityNo"].ToString() != "")
                {
                    model.FEntityNo = ds.Tables[0].Rows[0]["FEntityNo"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FGroupName"] != null && ds.Tables[0].Rows[0]["FGroupName"].ToString() != "")
                {
                    model.FGroupName = ds.Tables[0].Rows[0]["FGroupName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["FNote"] != null && ds.Tables[0].Rows[0]["FNote"].ToString() != "")
                {
                    model.FNote = ds.Tables[0].Rows[0]["FNote"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 删除用户组
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="groupID">用户组编号</param>
        /// <param name="fentityNo">组织机构编号</param>
        public void DeleteGroup(string groupID, string fentityNo)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            //删除用户组权限
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("DELETE FROM t_PrvGroupPriv WHERE FGroupNo=@FGroupNo and FEntityNo=@FEntityNo");
            SqlParameter[] parameters = {
                    new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10),
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)
                                        };
            parameters[0].Value = groupID;
            parameters[1].Value = fentityNo;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);


            //删除组织用户
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("DELETE FROM t_PrvUserGroup WHERE FGroupNo=@FGroupNo");
            SqlParameter[] parameters2 = {
                    new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10)
                                        };
            parameters2[0].Value = groupID;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters2;
            cmdList.Add(cmds);

            //删除用户组
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("DELETE FROM t_PrvGroup WHERE FGroupNo=@FGroupNo");
            SqlParameter[] parameters3 = {
                    new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10)
                                        };
            parameters3[0].Value = groupID;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters3;
            cmdList.Add(cmds);

            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
            }
        }

        /// <summary>
        /// 增加用户组
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="groupModel">用户组信息</param>
        /// <param name="userFuncList">权限信息列表</param>
        /// <param name="orgUserList">用户信息列表</param>
        /// <returns>增加是否成功</returns>
        public string AddPrvGroup(Model.PrvGroup groupModel, List<string> userFuncList, List<string> orgUserList)
        {
            string groupID = "0";
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            strSql.Append("INSERT INTO t_PrvGroup (FGroupName,FNote,FEntityNo,FGroupNo) VALUES (@FGroupName,@FNote,@FEntityNo,@FGroupNo)");
            //strSql.Append(";SELECT @returnId=IDENT_CURRENT('t_PrvGroup')");
            SqlParameter[] parameters = {
                    new SqlParameter("@FGroupName", SqlDbType.NVarChar,50),
                    new SqlParameter("@FNote", SqlDbType.NVarChar,255),
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12),
                    new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10)
                                        };
            parameters[0].Value = groupModel.FGroupName;
            parameters[1].Value = groupModel.FNote;
            parameters[2].Value = groupModel.FEntityNo;
            parameters[3].Value = groupModel.FGroupNo;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            //增加用户权限
            foreach (string userFunc in userFuncList)
            {
                strSql = new StringBuilder();
                cmds = new CommandInfo();
                strSql.Append("INSERT INTO t_PrvGroupPriv (FGroupNo,FFunctNo,FEntityNo) VALUES (@FGroupNo,@FFunctNo,@FEntityNo)");
                SqlParameter[] parameters1 = {
                    new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10),
                    new SqlParameter("@FFunctNo", SqlDbType.NVarChar,80),
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12)
                                        };
                parameters1[0].Value = groupModel.FGroupNo;
                parameters1[1].Value = userFunc;
                parameters1[2].Value = groupModel.FEntityNo;
                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters1;
                cmdList.Add(cmds);
            }

            //增加组织用户
            foreach (string orgUser in orgUserList)
            {
                strSql = new StringBuilder();
                cmds = new CommandInfo();
                strSql.Append("INSERT INTO t_PrvUserGroup (FLogID,FEntityNo,FGroupNo) VALUES (@FLogID,@FEntityNo,@FGroupNo)");
                SqlParameter[] parameters2 = {
                    new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
                    new SqlParameter("@FEntityNo",SqlDbType.NVarChar,12),
                    new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10)
                                        };
                parameters2[0].Value = orgUser;
                parameters2[1].Value = groupModel.FEntityNo;
                parameters2[2].Value = groupModel.FGroupNo;
                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters2;
                cmdList.Add(cmds);
            }

            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
            }
            groupID = groupModel.FGroupNo;
            return groupID;
        }

        /// <summary>
        /// 修改用户组
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="groupModel">用户组信息</param>
        /// <param name="userFuncList">权限信息列表</param>
        /// <param name="orgUserList">用户信息列表</param>
        /// <returns>增加是否成功</returns>
        public void EditPrvGroup(Model.PrvGroup groupModel, List<string> userFuncList, List<string> orgUserList)
        {
            StringBuilder strSql = new StringBuilder();
            List<CommandInfo> cmdList = new List<CommandInfo>();
            CommandInfo cmds = new CommandInfo();
            strSql.Append("UPDATE t_PrvGroup SET FGroupName=@FGroupName,");
            strSql.Append(" FNote=@FNote");
            strSql.Append(" WHERE FGroupNo=@FGroupNo");
            SqlParameter[] parameters = {
				    new SqlParameter("@FGroupName", SqlDbType.NVarChar,50),
					new SqlParameter("@FNote", SqlDbType.NVarChar,255),
                    new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10)
                                        };
            parameters[0].Value = groupModel.FGroupName;
            parameters[1].Value = groupModel.FNote;
            parameters[2].Value = groupModel.FGroupNo;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters;
            cmdList.Add(cmds);

            //更新用户权限
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("DELETE FROM t_PrvGroupPriv WHERE FGroupNo=@FGroupNo");
            SqlParameter[] parameters1 = {
                    new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10)
                                        };
            parameters1[0].Value = groupModel.FGroupNo;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters1;
            cmdList.Add(cmds);

            foreach (string userFunc in userFuncList)
            {
                strSql = new StringBuilder();
                cmds = new CommandInfo();
                strSql.Append("INSERT INTO t_PrvGroupPriv (FGroupNo,FFunctNo,FEntityNo) VALUES (@FGroupNo,@FFunctNo,@FEntityNo)");
                SqlParameter[] parameters2 = {
				    new SqlParameter("@FFunctNo", SqlDbType.NVarChar,80),
                    new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10),
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)
                                        };
                parameters2[0].Value = userFunc;
                parameters2[1].Value = groupModel.FGroupNo;
                parameters2[2].Value = groupModel.FEntityNo;
                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters2;
                cmdList.Add(cmds);

            }

            //更新组织用户
            strSql = new StringBuilder();
            cmds = new CommandInfo();
            strSql.Append("DELETE FROM t_PrvUserGroup WHERE FGroupNo=@FGroupNo");
            SqlParameter[] parameters3 = {
                    new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10)
                                        };
            parameters3[0].Value = groupModel.FGroupNo;
            cmds.CommandText = strSql.ToString();
            cmds.Parameters = parameters3;
            cmdList.Add(cmds);

            foreach (string orgUser in orgUserList)
            {
                strSql = new StringBuilder();
                cmds = new CommandInfo();
                strSql.Append("INSERT INTO t_PrvUserGroup (FGroupNo,FLogID,FEntityNo) VALUES (@FGroupNo,@FLogID,@FEntityNo)");
                SqlParameter[] parameters4 = {
                    new SqlParameter("@FLogID", SqlDbType.NVarChar,30),
                    new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10),
                    new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)
                                        };
                parameters4[0].Value = orgUser;
                parameters4[1].Value = groupModel.FGroupNo;
                parameters4[2].Value = groupModel.FEntityNo;
                cmds.CommandText = strSql.ToString();
                cmds.Parameters = parameters4;
                cmdList.Add(cmds);
            }

            if (cmdList.Count > 0)
            {
                DbHelperSQL.ExecuteSqlTranWithString(cmdList, 1800);
            }
        }
        /// <summary>
        /// 根据用户组编号获取人员列表
        /// <creator>wangl</creator>
        /// <date>2012-06-18</date>
        /// </summary>
        /// <param name="groupID"></param>
        /// <returns></returns>
        public List<string> GetLoginIDListByGroupID(decimal groupID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select FLogID ");
            strSql.Append(" FROM t_PrvUserGroup ");
            strSql.Append(" WHERE FGroupID=@FGroupID");
            SqlParameter[] parameters = {
					new SqlParameter("@FGroupID", SqlDbType.Decimal,18)
                                           };
            parameters[0].Value = groupID;
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            List<string> loginIDList = new List<string>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                loginIDList.Add(ds.Tables[0].Rows[i]["FLogID"].ToString());
            }

            return loginIDList;
        }
        ///// <summary>
        ///// 删除一条数据
        ///// <creator>zhongyl</creator>
        ///// <date>2013-07-12</date>
        ///// </summary>
        //public bool Delete(string fGroupNo, string fEntityNo)
        //{

        //    StringBuilder strSql = new StringBuilder();
        //    strSql.Append("delete from t_PrvGroup ");
        //    strSql.Append(" where FGroupNo=@FGroupNo and FEntityNo=@FEntityNo ");
        //    SqlParameter[] parameters = {
        //            new SqlParameter("@FGroupNo", SqlDbType.NVarChar,10),
        //            new SqlParameter("@FEntityNo", SqlDbType.NVarChar,12)			};
        //    parameters[0].Value = fGroupNo;
        //    parameters[1].Value = fEntityNo;

        //    int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
        //    if (rows > 0)
        //    {
        //        return true;
        //    }
        //    else
        //    {
        //        return false;
        //    }
        //}
    }
}
